﻿using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Data.SqlServerCe;
using Mimoza.Common;

namespace Mimoza.DBLayer.SQLSC
{
    static class SQLCS_Helper
    {
        const string c_serviceDBName = "Service.sdf";
        const string c_hosyDBName    = "Host.sdf";

        static string[] createTables = new string[]
        { @"CREATE TABLE Containers( 
                ID UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY, 
                ParentID UNIQUEIDENTIFIER, 
                Name NVARCHAR(200), 
                Type INTEGER,
                IsSystem BIT, 
                IsVisible BIT, 
                EffectivePolicyID UNIQUEIDENTIFIER, 
                UpdateDate DATETIME)",
          @"CREATE TABLE Hosts     ( 
                ID UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY, 
                ParentID UNIQUEIDENTIFIER, 
                Name NVARCHAR(200), 
                NewName NVARCHAR(200), 
                Description NVARCHAR(200), 
                MACAddress NVARCHAR(512), 
                IPAddress NVARCHAR(512),
                AdaptersInfo NTEXT, 
                OS NVARCHAR(100), 
                Model NVARCHAR(100), 
                Version NVARCHAR(100), 
                Status INTEGER, 
                ModificationID UNIQUEIDENTIFIER, 
                UpdateDate DATETIME,
                LastConnectionDate DATETIME)",
          @"CREATE TABLE Policy    ( 
                ID UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY,
                ParentID UNIQUEIDENTIFIER, 
                Name NVARCHAR(200), 
                Description NVARCHAR(200), 
                PluginID NVARCHAR(200), 
                IsEnabled BIT, 
                IsHardware BIT, 
                IsMultiple BIT, 
                Override BIT, 
                Data NTEXT, 
                UpdatesID UNIQUEIDENTIFIER, 
                DataModificationID UNIQUEIDENTIFIER, 
                UpdateDate DATETIME)",
          @"CREATE TABLE Updates  ( 
                ID UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY, 
                Name NVARCHAR(200), 
                Description NVARCHAR(200), 
                OS NVARCHAR(100), 
                Model NVARCHAR(100), 
                Version NVARCHAR(100), 
                UpdateFileName NVARCHAR(1024))",
          @"CREATE TABLE Tasks     ( 
                ID UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY, 
                Name NVARCHAR(200), 
                Description NVARCHAR(200), 
                PluginID NVARCHAR(200), 
                Data NTEXT, 
                InProgress BIT, 
                CreateDate DATETIME)",
          "ALTER TABLE Containers ADD CONSTRAINT uniq_NameInCont UNIQUE (ParentID, Name)",          
          "ALTER TABLE Updates    ADD CONSTRAINT uniq_UpdateName UNIQUE (Name)",
          "ALTER TABLE Policy     ADD CONSTRAINT fk_Updates_ID_ID FOREIGN KEY (UpdatesID) REFERENCES Updates(ID)"
        };

        //00-40-63-F5-07-64
        //192.168.111.111

        static string[] insertServiceData = new string[]
        {
            "INSERT INTO Containers VALUES('83399760-CC66-44f6-B108-D4991978E364',                                   NULL, 'localhost'      , -1, 1, 0, '83399760-CC66-44f6-B108-D4991978E367', '1947/08/15 03:33:20')",
            "INSERT INTO Containers VALUES('83399760-CC66-44f6-B108-D4991978E365', '83399760-CC66-44f6-B108-D4991978E364', '%MANAGED%' ,  0, 1, 1, '83399760-CC66-44f6-B108-D4991978D368', '1947/08/15 03:33:20')",
            "INSERT INTO Containers VALUES('83399760-CC66-44f6-B108-D4991978E366', '83399760-CC66-44f6-B108-D4991978E364', '%UNMANAGED%' ,  1, 1, 1, '83399760-CC66-44f6-B108-D4991978E369', '1947/08/15 03:33:20')",
            "INSERT INTO Containers VALUES('83399760-CC66-44f6-B108-D4991978E367', '83399760-CC66-44f6-B108-D4991978E364', '%QUERIES%' ,  2, 1, 1, NULL, '1947/08/15 03:33:20')",
                "INSERT INTO Containers VALUES('83399760-CC66-44f6-B108-D4991978E391', '83399760-CC66-44f6-B108-D4991978E367', '%BYCONNECTIONTIME%' ,  5, 1, 1, NULL, '1947/08/15 03:33:20')",
                "INSERT INTO Containers VALUES('83399760-CC66-44f6-B108-D4991978E392', '83399760-CC66-44f6-B108-D4991978E367', '%BYPLATFORM%' ,  6, 1, 1, NULL, '1947/08/15 03:33:20')",
            "INSERT INTO Containers VALUES('83399760-CC66-44f6-B108-D4991978E368', '83399760-CC66-44f6-B108-D4991978E364', '%UPDATES%' ,  4, 1, 1, NULL, '1947/08/15 03:33:20')",
/*            "INSERT INTO Policy VALUES('5BE7EC7F-5EDF-4cb5-A201-AD5C5F66EE36','83399760-CC66-44f6-B108-D4991978E365', 'Policy1', 'Policy Description1', 'Mimoza.Plugins.MyPolicy1' , 1, 0, 0, 0, NULL, NULL, '2B6786CD-3A95-4c45-9463-3E12CFCC8C2D', '1947/08/15 03:33:20')",
            "INSERT INTO Policy VALUES('14C59A45-C4A1-40f7-A81F-462C36D7FCAD','83399760-CC66-44f6-B108-D4991978E365', 'Policy2', 'Policy Description2', 'Mimoza.Plugins.MyPolicy2' , 1, 0, 0, 0, NULL, NULL, '2B6786CD-3A95-4c45-9463-3E12CFCC8C2D', '1947/08/15 03:33:20')",
            "INSERT INTO Hosts VALUES('51E9FC28-BEE1-4e0d-8EC9-12038F446ABD','83399760-CC66-44f6-B108-D4991978E365', 'Host1', 'Host Description1', '001BEB300048' , '192.168.234.12', '','CE 6.0 R3', 'L66', '1.1.0', 0, '00000000-0000-0000-0000-000000000000', '1947/08/15 03:33:20', '1947/08/15 03:33:20')",
            "INSERT INTO Hosts VALUES('8D7CA257-2179-4db1-9461-FDB7324F13A2','83399760-CC66-44f6-B108-D4991978E365', 'Host2', 'Host Description2', '0025110F3971' , '192.168.24.121', '','CE 6.0 R3', 'L66', '1.1.0',0, '2B6786CD-3A95-4c45-9463-3E12CFCC8C2D', '1947/08/15 03:33:20', '1947/08/15 03:33:20')",
            "INSERT INTO Hosts VALUES('FDF40846-E4D9-47f6-9B37-77541CA3D31A','83399760-CC66-44f6-B108-D4991978E366', 'Host3', 'Host Description3', '0025110F377E' , '192.168.23.122', '','CE 6.0 R3', 'L66', '1.1.0',0, '2B6786CD-3A95-4c45-9463-3E12CFCC8C2D', '1947/08/15 03:33:20', '1947/08/15 03:33:20')",
            "INSERT INTO Hosts VALUES('A5BFB14F-B3B1-4591-A50C-BFF61A27ADE5','83399760-CC66-44f6-B108-D4991978E366', 'Host4', 'Host Description4', '004063D9858D' , '192.168.234.14', '','CE 6.0 R3', 'L66', '1.1.0',0, '2B6786CD-3A95-4c45-9463-3E12CFCC8C2D', '1947/08/15 03:33:20', '1947/08/15 03:33:20')",
            "INSERT INTO Updates VALUES('A5BFB14F-B3B1-4591-A50C-BFF61A27ADE6', 'Update_CE5_12.34.5', 'Update description', 'Windows CE 5.0' , 'L66-CE5', '1.1.0.RC3', 'Data/UpdateRepository/file1')",
            "INSERT INTO Updates VALUES('A5BFB14F-B3B1-4591-A50C-BFF61A27ADE7', 'Update_CE6_12.34.5', 'Update description 6', 'Windows CE 6.0' , 'L66', '1.1.0', 'Data/UpdateRepository/file2')"
*/        };

        static string[] insertHostData = new string[]
        {
            "INSERT INTO Containers VALUES('83399760-CC66-44f6-B108-D4991978E364',                                  NULL, 'Root'      , -1, 1, 1, NULL, '1947/08/15 03:33:20')"
            //,"INSERT INTO Containers VALUES('B43B923B-E4D2-41db-A84F-093FA6B2073D','34F87884-67B1-45ca-87DA-5BC9BACA427E', 'localhost' , -1, 1, 1, NULL, '1947/08/15 03:33:20')"
        };
        //public const string SelectContainersString = "SELECT ID, ParentID, Name, IsSystem, EffectivePolicyData, EffectivePolicyID, UpdateDate FROM Containers";
        //public const string SelectHostsString = "SELECT ID, ParentID, Name, Description, MACAddress, IPAddress, OS, Status, UpdateDate FROM Hosts";
        //public const string SelectPolicyString = "SELECT ID, ParentID, Name, Description, PluginID, Data, UpdateDate FROM Policy";

        public const string SelectContainersString = "SELECT * FROM Containers";
        public const string SelectHostsString = "SELECT * FROM Hosts";
        public const string SelectPolicyString = "SELECT * FROM Policy";
        public const string SelectUpdateString = "SELECT * FROM Updates";

        static public string GetDBName(string dbName)
        {
            return (dbName == null) ? c_serviceDBName : dbName;
        }
        static public string GetConnectionString(string dbName)
        {
            return "Data Source = " + GetDBName(dbName);
        }
        static public bool IsDBExists(string dbName)
        {
            if (File.Exists(GetDBName(dbName)))
            {
                return true;
            }

            return false;
        }        
        static public void CreateDB(string dbName, bool serviceDB)
        {
            SqlCeConnection conn = null;
            string[] insertData = null;

            SqlCeEngine engine = new SqlCeEngine(GetConnectionString(dbName));
            engine.CreateDatabase();

            conn = new SqlCeConnection(GetConnectionString(dbName));
            conn.Open();
            SqlCeCommand cmd = conn.CreateCommand();
            foreach (string command in createTables)
            {
                cmd.CommandText = command;
                cmd.ExecuteNonQuery();
            }

            if (serviceDB)
            {
                insertData = insertServiceData;
            } else 
            {
                insertData = insertHostData;
            }

            foreach (string command in insertData)
            {   
                cmd.CommandText = PrepareCommand(command);
                cmd.ExecuteNonQuery();
            }
        }
        static public string PrepareCommand(string command)
        {
            command = command.Replace("%MANAGED%", Resource.ManagedContainerName);
            command = command.Replace("%UNMANAGED%", Resource.UnmanagedContainerName);
            command = command.Replace("%UPDATES%", Resource.UpdatesContainerName);

            command = command.Replace("%QUERIES%", Resource.QueriesContainerName);
            command = command.Replace("%BYCONNECTIONTIME%", Resource.QueryByTimeContainerName);
            command = command.Replace("%BYPLATFORM%", Resource.QueryByPlatformContainerName);

            return command;
        }
        static public void DeleteDB(string dbName)
        {
            File.Delete(GetDBName(dbName));
        }
        static public ContainerRecord ContainerRecordFromResultSet(SqlCeResultSet rs, string[] columns)
        {
            ContainerRecord record = new ContainerRecord();

            RecordFromResultSet(record, rs, columns);

            return record;
        }
        static public HostRecord HostRecordFromResultSet(SqlCeResultSet rs, string[] columns)
        {
            HostRecord record = new HostRecord();

            RecordFromResultSet(record, rs, columns);

            return record;
        }
        static public PolicyRecord PolicyRecordFromResultSet(SqlCeResultSet rs, string[] columns)
        {
            PolicyRecord record = new PolicyRecord();
            
            RecordFromResultSet(record, rs, columns);

            return record;
        }
        static public UpdateRecord UpdateRecordFromResultSet(SqlCeResultSet rs, string[] columns)
        {
            UpdateRecord record = new UpdateRecord();

            RecordFromResultSet(record, rs, columns);

            return record;
        }
        static public void RecordFromResultSet(object record, SqlCeResultSet resultSet, string [] columns)
        {
            foreach (FieldInfo info in record.GetType().GetFields())
            {
                object val = null;
                int index = 0;

                foreach (string str in columns)
                {
                    if (str == info.Name)
                    {
                        if (!resultSet.IsDBNull(index))
                        {
                            val = resultSet.GetValue(index);
                            info.SetValue(record, val);
                        }
                        break;
                    }
                    index++;
                }
            }
        }
        static public bool GreateInsertCommand(SqlCeCommand command, string table, Object record)
        {
            bool first = true;
            string fields = " (";
            string values = "VALUES(";
            foreach (FieldInfo info in record.GetType().GetFields())
            {
                if (info.IsNotSerialized) break;

                object val = info.GetValue(record);
                if (val is Guid && (Guid)val == Guid.Empty) val = null;
                if (null != val)
                {
                    if (!first)
                    {
                        fields += ",";
                        values += ",";
                    }
                    first = false;
                    fields += " [" + info.Name + "]";
                    values += " @" + info.Name;
                    command.Parameters.AddWithValue("@" + info.Name, val);
                }
            }
            
            fields += ") ";
            values += ")";

            command.CommandText = "INSERT INTO " + table + fields + values;

            return !first;
        }
        static public bool GreateUpdateCommand(SqlCeCommand command, string table, Object record)
        {
            bool first = true;
            string fields = "";

            foreach (FieldInfo info in record.GetType().GetFields())
            {
                object val = info.GetValue(record);
                if (val is Guid && (Guid)val == Guid.Empty) val = null;
                if (null != val)
                {
                    if (!first)
                    {
                        fields += ",";
                    }
                    first = false;
                    fields += " [" + info.Name + "] = @" + info.Name;
                    
                    command.Parameters.AddWithValue("@" + info.Name, val);
                }
            }

            command.CommandText = "UPDATE " + table + " SET " + fields + " WHERE[ID] = @ID";

            return !first;
        }
    }
}
